package com.opennetexam.manager.mapper;

import com.opennetexam.manager.databasemodel.DeregulationItem;
import com.opennetexam.manager.databasemodel.ExamingMonitor;
import com.opennetexam.manager.dto.ExamPlanInfoTime;
import com.opennetexam.manager.dto.ExamStudentInfoViewModel;
import com.opennetexam.manager.dto.ExaminerMoniterDTO;
import com.opennetexam.manager.sqlprovider.ExamMoniterProvider;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

/**
 * @author 奶茶
 * Created on 2017/11/15.
 */
@Mapper
public interface ExamMoniterMapper {

    //@SelectProvider(type = ExamMoniterProvider.class,method = "findUnifiedExamList")
    @Select("SELECT \n" +
            "dataplan.AppID,\n" +
            "dataplan.ExamPlanInfoID,\n" +
            "dataplan.ExamPlanName AS ExamPlanInfoName,\n" +
            "dataplan.SeatByNumberRequired,\n" +
            "dataplan.ExamMode,\n" +
            "dataplan.EndTime,\n" +
            "dataroom.ExamRoomID,\n" +
            "dataroom.ExamRoomName,\n" +
            "datascene.ExamSceneID,\n" +
            "datascene.ExamSceneName,\n" +
            "datascene.PlanBeginTime AS ExamSceneBeginTime\n" +
            "FROM\n" +
            "examplaninfo dataplan\n" +
            "JOIN examscenes datascene ON datascene.ExamPlanInfoID = dataplan.ExamPlanInfoID\n" +
            "JOIN examroom dataroom ON dataroom.ExamPlanInfoID = datascene.ExamPlanInfoID\n" +
            "WHERE\n" +
            "dataplan.ExamSiteID = #{examSiteId}\n" +
            "AND dataplan.ExamMode = #{unifiedExam} AND dataplan.ExamPlanType = #{planType} AND dataplan.SysState = #{examPlanSysState}\n" +
            "AND dataplan.EndTime > NOW()")
    List<ExamPlanInfoTime> findUnifiedExamList(@Param(value = "examSiteId") String examSiteId, @Param(value = "planType") Short planType, @Param(value = "unifiedExam") Integer unifiedExam,@Param(value = "examPlanSysState") Integer examPlanSysState);

    //@SelectProvider(type = ExamMoniterProvider.class,method = "findFreeExamList")
    @Select("SELECT \n" +
            "dataplan.AppID,\n" +
            "dataplan.ExamPlanInfoID,\n" +
            "dataplan.ExamPlanName AS ExamPlanInfoName,\n" +
            "dataplan.SeatByNumberRequired,\n" +
            "dataplan.ExamMode,\n" +
            "dataplan.EndTime,\n" +
            "dataroom.ExamRoomID,\n" +
            "dataroom.ExamRoomName,\n" +
            "datascene.ExamSceneID,\n" +
            "datascene.ExamSceneName,\n" +
            "datascene.PlanBeginTime AS ExamSceneBeginTime\n" +
            "FROM\n" +
            "examplaninfo dataplan\n" +
            "JOIN examscenes datascene ON datascene.ExamPlanInfoID = dataplan.ExamPlanInfoID\n" +
            "JOIN examroom dataroom ON dataroom.ExamPlanInfoID = datascene.ExamPlanInfoID\n" +
            "WHERE\n" +
            "dataplan.ExamSiteID = #{examSiteId}\n" +
            "AND dataplan.ExamMode = #{freeExam} AND dataplan.ExamPlanType = #{planType} AND dataplan.SysState = #{examPlanSysState}\n" +
            "AND dataplan.EndTime > NOW()")
    List<ExamPlanInfoTime> findFreeExamList(@Param(value = "examSiteId") String examSiteId, @Param(value = "planType") Short planType, @Param(value = "freeExam") Integer freeExam,@Param(value = "examPlanSysState") Integer examPlanSysState);

    @SelectProvider(type = ExamMoniterProvider.class,method = "findExamingMonitorsByStats")
    List<ExamingMonitor> findExamingMonitorsByStats(@Param(value = "sysState") Integer sysState);

    @SelectProvider(type = ExamMoniterProvider.class,method = "findExamCaseList")
    List<ExaminerMoniterDTO> findExamCaseList(@Param(value = "appId") String appId, @Param(value = "siteId") String siteId, @Param(value = "planId") String planId, @Param(value = "roomId") String roomId, @Param(value = "sysState") Integer sysState, @Param(value = "subjectId") String subjectId, @Param(value = "sceneId") String sceneId, @Param(value = "examCardNo") String examCardNo, @Param(value = "examineeName") String examineeName);

    @Select("SELECT\n" +
            "\t\t\tCOUNT(1)\n" +
            "\t\tFROM\n" +
            "\t\t\tExamPhoto photo\n" +
            "\t\tWHERE\n" +
            "\t\t\tphoto.ExamineeArrangeID = #{examineeArrangeID}\n" +
            "\t\tAND photo.PhotoResourceType = 1\n" +
            "\t\tAND photo.FaceCompareResult = 0")
    Integer getFaceCompareFailCount(@Param(value = "examineeArrangeID") String examineeArrangeID);

    @SelectProvider(type = ExamMoniterProvider.class,method = "findExamingMonitorsBySomeWhere")
    List<ExamingMonitor> findExamingMonitorsBySomeWhere(@Param(value = "appID") String appID,@Param(value = "examPlanInfoID") String examPlanInfoID,@Param(value = "examRoomID") String examRoomID,@Param(value = "examSceneID") String examSceneID);

    @Update("UPDATE ExamingMonitor SET DelayMinute = #{delayMinute},RetainTime=#{retainTime},SysState=#{sysState},SubmitType=#{submitType},ExamEndTime=#{examEndTime},IfDeregulation=#{ifDeregulation} WHERE AppID = #{appID} AND ExamPlanInfoID = #{examPlanInfoID} AND ExamSubjectID = #{examSubjectID} AND ExamineeID = #{examineeID}")
    Integer updateTime(ExamingMonitor examingMonitor);

    @Update("UPDATE ExamingMonitor SET ExamSceneID = #{examSceneID},ExamRoomID = #{examRoomID},LoginTime = #{loginTime},ExamStartTime = #{examStartTime},IDCardResult = #{iDCardResult},IDCardTime = #{iDCardTime},IDCardReason = #{iDCardReason},DelayMinute = #{delayMinute},RetainTime=#{retainTime},SysState=#{sysState},SubmitType=#{submitType},ExamEndTime=#{examEndTime},IfDeregulation=#{ifDeregulation} WHERE AppID = #{appID} AND ExamPlanInfoID = #{examPlanInfoID} AND ExamSubjectID = #{examSubjectID} AND ExamineeID = #{examineeID}")
    Integer updateExamingMoniter(ExamingMonitor examingMonitor);

    @Select("SELECT * FROM ExamingMonitor WHERE AppID = #{appID} AND ExamPlanInfoID = #{examPlanInfoID} AND ExamRoomID = #{examRoomID} AND ExamSceneID = #{examSceneID} AND ExamSubjectID = #{examSubjectID} AND ExamineeID = #{examineeID} LIMIT 1")
    ExamingMonitor getExamMoniterBySomeWhere(@Param(value = "appID") String appID,@Param(value = "examPlanInfoID") String examPlanInfoID,@Param(value = "examRoomID") String examRoomID,@Param(value = "examSceneID") String examSceneID,@Param(value = "examSubjectID") String examSubjectID,@Param(value = "examineeID") String examineeID);

    @Select("SELECT x.* FROM ExamingMonitor AS x JOIN ExamineeArrange AS y ON x.AppID=y.AppID AND x.ExamPlanInfoID=y.ExamPlanInfoID AND x.ExamSubjectID=y.ExamSubjectID AND x.ExamineeID=y.ExamineeID WHERE y.ExamineeArrangeID = #{arrangeId} LIMIT 1")
    ExamingMonitor getExamMoniterBySomeWhereAndArrangId(@Param(value = "arrangeId") String arrangeId);

    @Select("SELECT * FROM DeregulationItem WHERE DeregulationItemID = #{deregulationItemID}")
    DeregulationItem getDeregulationItemById(@Param(value = "deregulationItemID") Short deregulationItemID);

    @SelectProvider(type = ExamMoniterProvider.class,method = "getExamStudentInfoViewModelBySomeWhere")
    ExamStudentInfoViewModel getExamStudentInfoViewModelBySomeWhere(@Param(value = "examineeId") String examineeId, @Param(value = "appId") String appId, @Param(value = "planId") String planId);

    @Select("SELECT * FROM DeregulationItem")
    List<DeregulationItem> findAll();

    @SelectProvider(type = ExamMoniterProvider.class,method = "getSignInList")
    List<Map<String,Object>> getSignInList(@Param("planId")String planId,@Param("roomId") String roomId,@Param("sceneId") String sceneId,@Param("examineeName") String examineeName,@Param("examCardNo") String examCardNo,@Param("statusId") Integer statusId,@Param("appId") String appID,@Param("examSiteId") String examSiteId);

    @Select("SELECT * FROM ExamingMonitor WHERE AppID = #{appID} AND ExamPlanInfoID = #{examPlanInfoID} AND ExamineeID = #{examineeID}")
    List<ExamingMonitor> findExamingMonitors(@Param("appID") String appID,@Param("examPlanInfoID") String examPlanInfoID,@Param("examineeID") String examineeID);

    @Update("UPDATE ExamingMonitor SET IDCardResult = #{iDCardResult},IDCardTime=#{iDCardTime},IDCardReason=#{iDCardReason} WHERE AppID = #{appID} AND ExamPlanInfoID = #{examPlanInfoID} AND ExamSubjectID = #{examSubjectID} AND ExamineeID = #{examineeID}")
    Integer updateSignIn(ExamingMonitor examingMonitor);

    @Select("SELECT * FROM ExamingMonitor WHERE ExamPlanInfoID = #{examPlanInfoID} AND ExamSubjectID = #{examSubjectID} AND ExamineeID = #{examineeID} LIMIT 1")
    ExamingMonitor findExamingMonitorsFirstOrDefault(@Param("appID")String appID,@Param("examPlanInfoID") String examPlanInfoID,@Param("examSubjectID") String examSubjectID,@Param("examineeID") String examineeID);

    @Select("SELECT \n" +
            "em.* \n" +
            "from ExamingMonitor em \n" +
            "LEFT JOIN Examinee e on em.ExamineeID = e.ExamineeID AND em.AppID = e.AppID AND em.ExamPlanInfoID = e.ExamPlanInfoID \n" +
            "WHERE em.AppID =#{appID} and em.ExamPlanInfoID = #{planId}  AND e.ExamSiteID = #{examSiteID} and e.CertificateNo =#{certificateNo} and e.ExamineeName =#{examineeName} ")
    List<ExamingMonitor> findAutoExamingMonitorsBySomeWhere(@Param("appID")String appID,@Param("examSiteID") String examSiteID,@Param("planId") String planId,@Param("examineeName") String examineeName,@Param("certificateNo") String certificateNo);

    @Insert("INSERT INTO ExamingMonitor (\n" +
            "\tAppID,ExamPlanInfoID,ExamSubjectID,ExamSceneID,ExamRoomID,ExamineeID,RetainTime,IDCardResult,IDCardReason\n" +
            ") SELECT #{appID},#{examPlanInfoID},#{examSubjectID},#{examSceneID},#{examRoomID},#{examineeID},#{retainTime},#{iDCardResult},#{iDCardReason}\n" +
            "FROM\n" +
            "\tDUAL\n" +
            "WHERE\n" +
            "\tNOT EXISTS (\n" +
            "\t\tSELECT\n" +
            "\t\t\t1\n" +
            "\t\tFROM\n" +
            "\t\t\tExamingMonitor\n" +
            "\t\tWHERE\n" +
            "\t\t\tExamPlanInfoID = #{examPlanInfoID}\n" +
            "\t\tAND ExamineeID = #{examineeID}\n" +
            "\t\tAND ExamSubjectID = #{examSubjectID}\n" +
            "\t\tAND ExamSceneID = #{examSceneID}\n" +
            "\t\tAND ExamRoomID = #{examRoomID}\n" +
            "\t\tLIMIT 1\n" +
            ")")
    Integer insertExamingMoniter(ExamingMonitor examingMonitor);

    @Insert("INSERT INTO ExamingMonitor (\n" +
            "\tAppID,ExamPlanInfoID,ExamSubjectID,ExamSceneID,ExamRoomID,ExamineeID,RetainTime,IDCardResult,IDCardReason\n" +
            ") VALUE (#{appID},#{examPlanInfoID},#{examSubjectID},#{examSceneID},#{examRoomID},#{examineeID},#{retainTime},#{iDCardResult},#{iDCardReason})\n")
    Integer insertExamingMoniterAll(ExamingMonitor examingMonitor);

    @Delete("DELETE FROM ExamingMonitor WHERE ExamPlanInfoID = #{planId}")
    void delExamMoniterByPlanId(@Param("planId")String planId);

    @Select("SELECT * FROM ExamingMonitor WHERE AppID = #{appId} AND ExamPlanInfoID = #{planId} AND ExamSceneID = #{sceneId} AND ExamineeID = #{examineeId}")
    ExamingMonitor findExamingMonitorsFirstOrDefaultOne(@Param("appId")String appId,@Param("planId") String planId,@Param("examineeId") String examineeId,@Param("sceneId") String sceneId);

    @Select("SELECT * FROM ExamingMonitor WHERE SysState > #{sysState} AND AppID = #{appID} AND ExamPlanInfoID = #{examPlanInfoID} AND ExamSubjectID = #{examSubjectID}")
    List<ExamingMonitor> findExamingMoniterWhereLogin(@Param(value = "appID") String appID,@Param(value = "examPlanInfoID") String examPlanInfoID,@Param(value = "examSubjectID") String examSubjectID,@Param(value = "sysState") Integer sysState);

    @Select("SELECT * FROM ExamingMonitor WHERE AppID = #{appID} AND ExamPlanInfoID = #{examPlanInfoID} AND ExamSubjectID = #{examSubjectID}")
    List<ExamingMonitor> findExamingMonitersByWhere(@Param(value = "appID") String appID,@Param(value = "examPlanInfoID") String examPlanInfoID,@Param(value = "examSubjectID") String examSubjectID);

    @Select("SELECT \n" +
            "em.*  \n" +
            "FROM ExamingMonitor em \n" +
            "INNER JOIN ExamPlanInfo epi on em.ExamPlanInfoID = epi.ExamPlanInfoID \n" +
            "INNER JOIN ExamineeArrange ea on em.ExamineeID = ea.ExamineeID \n" +
            "WHERE em.AppID = epi.AppID \n" +
            "AND em.ExamineeID = ea.ExamineeID \n" +
            "AND em.ExamPlanInfoID = ea.ExamPlanInfoID \n" +
            "AND em.ExamSubjectID = ea.ExamSubjectID \n" +
            "AND em.ExamSceneID = ea.ExamSceneID \n" +
            "AND em.ExamRoomID = ea.ExamRoomID \n" +
            "AND epi.ExamMode = #{unifiedExam} \n" +
            "AND epi.IsUnionPay = #{systemYes} \n" +
            "AND ea.PlanBeginTime > #{dateStartTime} \n" +
            "AND ea.PlanEndTime < #{dateEndTime} \n" +
            "AND em.ExamineeID = #{examineeId}")
    List<ExamingMonitor> findNowExamingMonitors(@Param("examineeId") Integer examineeId,@Param("dateStartTime") String dateStartTime,
                                                @Param("dateEndTime") String dateEndTime,@Param("unifiedExam") int unifiedExam,
                                                @Param("systemYes") int systemYes);

    @Select("SELECT * FROM ExamingMonitor \n" +
            "WHERE AppID = #{appId} AND ExamPlanInfoID = #{examPlanInfoId} AND ExamSubjectID = #{examSubjectId} \n" +
            "AND ExamineeID = #{examineeId} AND SysState = #{examManagerState} LIMIT 1")
    ExamingMonitor findDisciplineExamingMonitors(@Param("appId") String appId,@Param("examPlanInfoId") String examPlanInfoId,
                                                 @Param("examSubjectId") String examSubjectId,@Param("examineeId") String examineeId,
                                                 @Param("examManagerState") Integer examManagerState);

    @Select("SELECT * FROM ExamingMonitor WHERE AppID = #{appId} AND ExamPlanInfoID = #{planId} " +
            "AND ExamSubjectID = #{subjectId} AND ExamineeID = #{examineeId} AND IDCardResult != #{signInStatus} LIMIT 1")
    ExamingMonitor findExamingMonitorsBySignStatus(@Param("appId") String appId,@Param("planId") String planId,
                                                   @Param("subjectId") String subjectId,@Param("examineeId") String examineeId,
                                                   @Param("signInStatus") Integer signInStatus);
    @Select("SELECT * FROM ExamingMonitor WHERE ExamPlanInfoID = #{examPlanInfoId}" +
            " AND ExamSceneID = #{examSceneId} AND ExamRoomID = #{examRoomId}" +
            " AND ExamSubjectID = #{examSubjectId} AND ExamineeID = #{examineeId} LIMIT 1")
    ExamingMonitor findExamingMonitorsByIsSubmited(@Param("examPlanInfoId") String examPlanInfoId,@Param("examSceneId") String examSceneId,@Param("examRoomId") String examRoomId,
                                         @Param("examSubjectId") String examSubjectId,@Param("examineeId") String examineeId);

    @Select("SELECT ExamPlanInfoID,ExamSubjectID,ExamSceneID,ExamineeID,ExamStartTime,RetainTime FROM examingmonitor WHERE SysState = 2 AND SubmitType = 0")
    List<ExamingMonitor> findAllUNSubmitPaperInfo();

    @Update("UPDATE examingmonitor SET SysState = #{sysState},SubmitType = #{submitType},RetainTime = #{retainTime} WHERE ExamPlanInfoID = #{examPlanInfoID} AND ExamSubjectID = #{examSubjectID} AND ExamSceneID = #{examSceneID} AND ExamineeID = #{examineeID}")
    Integer updateExamingMonitorSubmitANDSysState(ExamingMonitor monitor);

}
